﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;

namespace ServerService {
	/// <summary>
	/// Summary description for Service
	/// </summary>
	[WebService(Namespace = "http://cbartosiak.studentlive.pl/")]
	[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
	[System.ComponentModel.ToolboxItem(false)]
	// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
	// [System.Web.Script.Services.ScriptService]

    public class Service : System.Web.Services.WebService
    {
        private string connectionString = "Data Source=clea.mic.psnc.pl;Initial Catalog=cbartosiak.studentlive.pl;"
                                + "User ID=cbartosiak;Password=fltvMe9FcF";
        //private string connectionString = "Data Source=(local);Initial Catalog=Chat;Integrated Security=True";
        private SqlConnection connection;
        private SqlCommand command;
        private string query;

        [WebMethod]
        public bool LogIn(string name)
        {
            bool logged = false; //Success
            bool isCurLogged = false; //Is user logged, currently
            SqlDataReader found;

            using (connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    query = @"SELECT * 
                              FROM Users 
                              WHERE name='" + name + "'";

                    command = new SqlCommand(query, connection);
                    found = command.ExecuteReader();

                    //Name of this user is currently in db 'Chat'
                    if (found.HasRows)
                    {
                        found.Read();
                            isCurLogged = found.GetBoolean(2);
                        found.Close();

                        if (isCurLogged) logged = false;
                        else
                        {
							query = @"UPDATE Users SET isLoggedIn=1 
                                      WHERE name='" + name + "'";

                            command = new SqlCommand(query, connection);
                            command.ExecuteNonQuery();
                            logged = true;
                        }
                    }
                    else //This is new user
                    {
                        found.Close();
                        query = "INSERT INTO Users (ID, name, isLoggedIn) " +
                                "VALUES (NEWID(),'" + name + "', 1)";

                        command = new SqlCommand(query, connection);
                        command.ExecuteNonQuery();

                        logged = true;
                    }
                }
                catch (Exception)
                {
                    return false;
                }
                finally
                {
                    connection.Close();
                }
                return logged;
            }

        }

        [WebMethod]
        public List<Object[]> GetNewMessages(DateTime time, string roomName)
        {
            List<Object[]> newMsg = new List<Object[]>();

            using (connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    //get id
                    query = "SELECT ID FROM Rooms WHERE name = '" + roomName + "'";
                    command = new SqlCommand(query, connection);
                    var ID = command.ExecuteScalar();
                    
                    query = @"SELECT DISTINCT u.name, m.body, m.date 
                              FROM Messages m, Users u, Rooms r
                              WHERE m.UserID=u.ID AND m.RoomID='"+ID+"' AND m.date >= '" + time + "'";

                    command = new SqlCommand(query, connection);
                    SqlDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        newMsg.Add(new Object[3] { reader.GetString(0), reader.GetString(1), reader.GetDateTime(2) });
                    }

                    IComparer<Object[]> myComp = new myCompare();
                    newMsg.Sort(myComp);

                }
                catch (Exception)
                {
                    newMsg = null;
                }
                finally
                {
                    connection.Close();
                }
            }
            
            return newMsg;
        }

        [WebMethod]
        public void PostMessage(string body, string name)
        {
            using (connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    //get user id
                    query = @"SELECT ID 
                              FROM Users 
                              WHERE name='" + name + "'";
                    command = new SqlCommand(query, connection);
                    var id = command.ExecuteScalar();


                    //get room id
                    query = @"SELECT RoomID 
                              FROM Users
                              WHERE name='" + name + "'";
                    command = new SqlCommand(query, connection);
                    var RoomID = command.ExecuteScalar();


                    //if it exist insert into tabel new msg
                    if (id != null)
                    {
                        query = "INSERT INTO Messages VALUES(NEWID(),'" +
                                body + "','" +
                                DateTime.Now + "','" +
                                id + "','"+RoomID+"')";

                        command = new SqlCommand(query, connection);
                        command.ExecuteNonQuery();
                    }

                }
                finally
                {
                    connection.Close();
                }
            }
        }

        [WebMethod]
        public void LogOut(string name)
        {
            using (connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    query = @"UPDATE Users SET isLoggedIn=0 
                             WHERE name='" + name + "'";

                    command = new SqlCommand(query, connection);
                    command.ExecuteNonQuery();

                }
                finally
                {
                    connection.Close();
                }
            }
        }

        [WebMethod]
        public List<string> GetLoggedUsers(string roomName)
        {
            List<string> loggedUsers;

            using (connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    //get room ID
                    query = @"SELECT ID
                              FROM Rooms
                              WHERE name = '" + roomName + "'";
                    
                    command = new SqlCommand(query, connection);
                    var RoomID = command.ExecuteScalar();

                    query = @"SELECT name 
                              FROM Users
                              WHERE isLoggedIn=1 AND RoomID='" + RoomID + "'";

                    command = new SqlCommand(query, connection);
                    SqlDataReader reader = command.ExecuteReader();

                    if (!reader.HasRows)
                        loggedUsers = null;
                    else //if we found any row
                        loggedUsers = new List<string>();

                    while (reader.Read())
                        loggedUsers.Add(reader.GetString(0));

                    reader.Close();
                }
                finally
                {
                    connection.Close();
                }
            }
            loggedUsers.Sort();
            return loggedUsers;
        }
        
        [WebMethod]
        public DateTime GetDateTimeNow() {
			return DateTime.Now;
        }

        [WebMethod]
        public void EnterRoom(string usrName, string roomName)
        {
            using (connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    query = @"SELECT ID
                              FROM Rooms
                              WHERE name = '" + roomName + "'";

                    command = new SqlCommand(query, connection);
                    var ID = command.ExecuteScalar();
                    
                    //set room for current selected usrName

                    query = "UPDATE Users SET RoomID = '" + ID.ToString() + "'" +
                            "WHERE name = '" + usrName + "'";

                    command = new SqlCommand(query, connection);
                    command.ExecuteNonQuery();
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        [WebMethod]
        public List<string> GetRooms()
        {
            List<string> rooms = null;

            using (connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    query = @"SELECT name
                              FROM Rooms";

                    command = new SqlCommand(query, connection);
                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        rooms = new List<string>();
                        while (reader.Read())
                        {
                            rooms.Add(reader.GetString(0));
                        }
                    }
                }
                finally
                {
                    connection.Close();
                }
            }

            return rooms;
        }

        [WebMethod]
        public void AddRoom(string name)
        {
            using (connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    query = "INSERT INTO Rooms VALUES (NEWID(), '" + name + "')";

                    command = new SqlCommand(query, connection);
                    command.ExecuteNonQuery();

                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }
}
